﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Data;

namespace VietKinsNutrition.Data
{
    public class ClsTblDmTruonghocDAL
    {
        //ClsConnection ClsCnn = new ClsConnection();
        public void Insert(int LoaitruongId, int LoaitruongchuanId, string TensoGD, string TenphongGD,
            string Tentruong, string Diachi, int IdXa, string Dienthoai, string Email, string Web, bool bValue, bool qdtl, bool Dunuocsach, bool noimang, double dientich)
        {

            //   string NamePrc = QueryString.SPROC_TBLTRUONG_ADD.Replace("@LoaitruongId", LoaitruongId.ToString()).Replace("@LoaitruongchuanId", LoaitruongchuanId.ToString()).Replace("@TensoGD", TensoGD).Replace("@TenphongGD", TenphongGD).Replace("@Tentruong", Tentruong).Replace("@Diachi", Diachi).Replace("@IdXa", IdXa == 0 ? "null" : IdXa.ToString()).Replace("@Dienthoai", Dienthoai).Replace("@Email", Email).Replace("@Web", Web).Replace("@Vungmien", bValue.ToString()).Replace("@qdtl", qdtl.ToString()); 

            string NamePrc = NamePrc = "INSERT INTO [TblTruong]"
                    + " 	([LoaitruongId],[LoaitruongchuanId],[TensoGD],[TenphongGD],[Tentruong],[Diachi],[IdXa],[Dienthoai],[Email],[Web],[Vungmien],[Quyetdinhtl], [Dunuocsach],[noimang],[Dientich] ) "
                    + " 	VALUES ( " + LoaitruongId + "," + LoaitruongchuanId + ",'" + TensoGD + "','" + TenphongGD + "',\"" + Tentruong + "\", \"" + Diachi + "\"," + IdXa.ToString() + ",'" + Dienthoai + "','" + Email + "','" + Web + "'," + bValue.ToString() + "," + qdtl.ToString() + ", " + Dunuocsach.ToString() + "," + noimang.ToString() + "," + dientich.ToString() + " ) ";
            OleDbCommand Cmm = new OleDbCommand();

            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public void Edit(int Id, int LoaitruongId, int LoaitruongchuanId, string TensoGD, string TenphongGD, string Tentruong, string Diachi, int IdXa, string Dienthoai, string Email, string Web, bool bValue, bool qdtl, bool Dunuocsach, bool Noimang, double dientich)
        {
            string NamePrc = QueryString.SPROC_TBLTRUONG_UPDATE.Replace("@LoaitruongId", LoaitruongId.ToString()).Replace("@LoaitruongchuanId", LoaitruongchuanId.ToString()).Replace("@TensoGD", TensoGD).Replace("@TenphongGD", TenphongGD).Replace("@Tentruong", Tentruong).Replace("@Diachi", Diachi).Replace("@IdXa", IdXa == 0 ? "null" : IdXa.ToString()).Replace("@Dienthoai", Dienthoai).Replace("@Email", Email).Replace("@Web", Web).Replace("@Vungmien", bValue.ToString()).Replace("@qdtl", qdtl.ToString()).Replace("@Id", Id.ToString()).Replace("@Noimang", Noimang.ToString()).Replace("@Dunuocsach", Dunuocsach.ToString()).Replace("@Dientich", dientich.ToString());
            OleDbCommand Cmm = new OleDbCommand();

            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public DataTable Nidi()
        {
            string NamePrc = "select * from TblNidi";
            string NameTable = "TblTruong";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;


            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            OleDbCommand Cmm = new OleDbCommand();

            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
                Dap = new OleDbDataAdapter(Cmm);
                Dap.Fill(Dset.Tables[NameTable]);
                BindingSource Binding = new BindingSource(Dset, NameTable);
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            return Dset.Tables[NameTable];

        }

        public BindingSource SelectAll()
        {
            string NamePrc = QueryString.SPROC_TBLTRUONG_GET;
            string NameTable = "TblTruong";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            OleDbCommand Cmm = new OleDbCommand();

            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[NameTable]);
            BindingSource Binding = new BindingSource(Dset, NameTable);
            return Binding;
        }
        public DataTable TblTruong()
        {
            string NamePrc = QueryString.SPROC_TBLTRUONG_GET;
            string NameTable = "TblTruong";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;


            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            OleDbCommand Cmm = new OleDbCommand();

            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
                Dap = new OleDbDataAdapter(Cmm);
                Dap.Fill(Dset.Tables[NameTable]);
                BindingSource Binding = new BindingSource(Dset, NameTable);
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            return Dset.Tables[NameTable];

        }
    }
}
